from flask import request, url_for
from funcy import project, partial

from flask_restful import abort
from redash import models
from redash.handlers.base import (
    BaseResource,
    get_object_or_404,
    paginate,
    filter_by_tags,
    order_results as _order_results,
)
from redash.permissions import (
    can_modify,
    require_access,
    require_admin_or_owner,
    require_object_modify_permission,
    require_permission,
    require_admin
)
from redash.security import csp_allows_embeding
from redash.serializers import (
    DashboardSerializer,
    public_dashboard,
)
from sqlalchemy.orm.exc import StaleDataError

# 引入db，sql直接查询
from .base import db
import json

from redash.permissions import is_admin,is_admin_or_owner

from collections import Counter

# Ordering map for relationships
order_map = {
    "name": "lowercase_name",
    "-name": "-lowercase_name",
    "created_at": "created_at",
    "-created_at": "-created_at",
}

order_results = partial(
    _order_results, default_order="-created_at", allowed_orders=order_map
)


# 首页报表查询接口
class DashboardListResource(BaseResource):
    @require_permission("list_dashboards")
    def get(self):
        """
        Lists all accessible dashboards.

        :qparam number page_size: Number of queries to return per page
        :qparam number page: Page number to retrieve
        :qparam number order: Name of column to order by
        :qparam number q: Full text search term

        Responds with an array of :ref:`dashboard <dashboard-response-label>`
        objects.
        """
        isadmin = is_admin(self.current_user.id)
        isowner = is_admin_or_owner(self.current_user.id)
        if isadmin:
            search_term = request.args.get("q")

            if search_term:
                results = models.Dashboard.search(
                    self.current_org,
                    self.current_user.group_ids,
                    self.current_user.id,
                    search_term,
                )
            else:
                results = models.Dashboard.all(
                    self.current_org, self.current_user.group_ids, self.current_user.id
                )

            results = filter_by_tags(results, models.Dashboard.tags)

            # order results according to passed order parameter,
            # special-casing search queries where the database
            # provides an order by search rank
            ordered_results = order_results(results, fallback=not bool(search_term))

            page = request.args.get("page", 1, type=int)
            page_size = request.args.get("page_size", 20, type=int)

            response = paginate(
                ordered_results,
                page=page,
                page_size=page_size,
                serializer=DashboardSerializer,
            )

            if search_term:
                self.record_event(
                    {"action": "search", "object_type": "dashboard", "term": search_term}
                )
            else:
                self.record_event({"action": "list", "object_type": "dashboard"})

            # http://47.110.53.44:5000/api/dashboards
            # print("response",response)
            return response

        elif isowner:
            # 重新实现的方法,普通用户过虑报表权限
            search = request.args.get("q")
            search_tags = request.args.get("tags")
            order_sort = request.args.get("order")

            page = request.args.get("page", 1, type=int)
            page_size = request.args.get("page_size", 20, type=int)

            sql_search = f" and a.name like '%{search}%' "
            sql_tags = f" and '{search_tags}'= any (a.tags) "

            if search:
                sql_append = sql_search
            elif search_tags:
                sql_append = sql_tags
            elif search and search_tags:
                sql_append = sql_search + sql_tags
            else:
                sql_append = ""

            sql_text = """ select distinct a.updated_at ,a.created_at ,a.id ,a."version" ,a.slug ,a."name" ,a.user_id ,a.layout ,a.dashboard_filters_enabled ,a.is_archived ,a.is_draft ,a.tags,null as widgets,d.id as is_favorite,e.id as uid,e."name" as username ,e.email,e.profile_image_url from public.dashboards a
                    left join dashboard_groups b on a.id = b.dashboard_id 
                    left join widgets c on a.id = c.dashboard_id 
                    left join favorites d on a.id = d.object_id and d.object_type = 'Dashboard'  and d.user_id = :uid
                    left join users e on a.user_id = e.id

                    where a.is_archived = false and a.is_draft = false and b.group_id in :group_ids {0}
                    order by a.created_at desc                    
            """.format(sql_append)
            # 解析参数        
            group_list = tuple(self.current_user.group_ids)
            uid = self.current_user.id
            # offset 数量
            page_offset = 0

            result_list = []
            results = db.session.execute(sql_text,{"uid":uid,"group_ids":group_list}).fetchall()
            if not results:
                return {
                "count": 0,
                "page": 0,
                "page_size": 0,
                "results":[]
                }

            count = len(results)

            if page == 1:
                results_new = results[:page_size]
            else:
                results_new = results[(page * page_size)-page_size:page * page_size]

            for data in results_new:       
                if data[13]:
                    is_favorite = True
                else:
                    is_favorite = False

                if data[11]:
                    tags11 = data[11]
                else:
                    tags11 = []
                # "widgets": data[12],
                resjon = {
                    "id": data[2],
                    "slug": data[4],
                    "name": data[5],
                    "user_id": data[6],
                    "user": {
                    "id": data[14],
                    "name": data[15],
                    "email": data[16],
                    "profile_image_url": data[17]
                    },
                    "layout": data[7],
                    "dashboard_filters_enabled": data[8],
                    "widgets": data[12],
                    "is_archived": data[9],
                    "is_draft": data[10],
                    "tags": tags11,
                    "updated_at": data[0],
                    "created_at": data[1],
                    "version": data[3],
                    "is_favorite": is_favorite
                }
                result_list.append(resjon)

            return {
                "count": count,
                "page": page,
                "page_size": page_size,
                "results":result_list
                }
        


    @require_permission("create_dashboard")
    def post(self):
        """
        Creates a new dashboard.

        :<json string name: Dashboard name

        Responds with a :ref:`dashboard <dashboard-response-label>`.
        """
        dashboard_properties = request.get_json(force=True)
        dashboard = models.Dashboard(
            name=dashboard_properties["name"],
            org=self.current_org,
            user=self.current_user,
            is_draft=True,
            layout="[]",
        )
        models.db.session.add(dashboard)
        models.db.session.commit()
        return DashboardSerializer(dashboard).serialize()


# 增加报表筛选,没有的报表权限
class DashboardListResourceNewGroupID(BaseResource):
    @require_permission("list_dashboards")
    def get(self,group_id):
        """
        Lists all accessible dashboards.

        :qparam number page_size: Number of queries to return per page
        :qparam number page: Page number to retrieve
        :qparam number order: Name of column to order by
        :qparam number q: Full text search term

        Responds with an array of :ref:`dashboard <dashboard-response-label>`
        objects.
        """

        # results = models.Dashboard.all(
        #     self.current_org, self.current_user.group_ids, self.current_user.id
        # )
    
        sql_text = """
        select id,name,tags from public.dashboards a where not exists (select 1 from public.dashboard_groups b where a.id = b.dashboard_id and b.group_id = :gid) and a.is_archived = false and a.is_draft = false         
        order by a.id desc
        """
        results = db.session.execute(sql_text,{"gid":group_id}).fetchall()
        # print("sqltxt",results)
        result_json = []
        
        count = len(results)
        for value in results:
            id = value[0]
            name = value[1]
            if value[2]:
                tags = value[2]
            else:
                tags = []

            results = {                
                "id":id,
                "name":name,
                "group_id":group_id,
                "tags":tags                
            }
            result_json.append(results)        
        
        header_json = {
            "count": count,
            "page": 1,
            "page_size": 25,
            "results": result_json              
        }
        return header_json


# 增加报表筛选，已经存在的报表权限
class DashboardListResourceExistGroupID(BaseResource):
    @require_permission("list_dashboards")
    def get(self,group_id):
        """
        Lists all accessible dashboards.

        :qparam number page_size: Number of queries to return per page
        :qparam number page: Page number to retrieve
        :qparam number order: Name of column to order by
        :qparam number q: Full text search term

        Responds with an array of :ref:`dashboard <dashboard-response-label>`
        objects.
        """

        # results = models.Dashboard.all(
        #     self.current_org, self.current_user.group_ids, self.current_user.id
        # )

        # 归档和草稿也要显示，否者会出现角色无法删除
        # and a.is_archived = false and a.is_draft = false
        sql_text = """
        select id,name,tags from public.dashboards a where exists (select 1 from public.dashboard_groups b where a.id = b.dashboard_id and b.group_id = :gid)         
        order by a.id desc
        """
        results = db.session.execute(sql_text,{"gid":group_id}).fetchall()
        # print("sqltxt",results)
        result_json = []
        
        count = len(results)
        for value in results:
            id = value[0]
            name = value[1]
            if value[2]:
                tags = value[2]
            else:
                tags = []

            results = {                
                "id":id,
                "name":name,
                "group_id":group_id,
                "tags":tags                
            }
            result_json.append(results)        
        
        header_json = {
            "count": count,
            "page": 1,
            "page_size": 25,
            "results": result_json              
        }
        return header_json


# 写入报表和组的绑定关系
class DashboardListResourceGroupID(BaseResource):
    @require_admin
    def post(self):
        # 解析json参数
        dashboard_error = []
        data_list = request.get_json(True)
        if not data_list:
            return {"code":1,"result":"请选择报表"}
        # print("jsondata:",data_list)
        for data in data_list:            
            try:
                dashboard_id_value = data.get("dashboard_id")
                group_id_value = data.get("group_id")
                # print("dashboard_id_value参数:",dashboard_id_value,group_id_value)
                
                dashboardgroup = models.DashboardGroup(
                    dashboard_id=dashboard_id_value,
                    group_id=group_id_value,
                )            
                models.db.session.add(dashboardgroup)        
                result = models.db.session.commit()                
            except Exception as ex:
                result = str(ex)
                err_json = {
                    "dashboard_id":dashboard_id_value,
                    "group_id":group_id_value,
                    "error":result
                }
                dashboard_error.append(err_json)

        if dashboard_error:    
            result_json = {
                "code":1,
                "result":dashboard_error
            }
            return result_json
        else:
            result_json = {
                "code":0,
                "result":result
            }
            return result_json

    # 删除数据
    @require_admin
    def delete(self):
        # 解析json参数
        dashboard_error = []
        data_list = request.get_json(True)
        if not data_list:
            return {"code":1,"result":"请选择报表"}
        # print("jsondata:",data_list)
        for data in data_list:            
            try:
                dashboard_id_value = data.get("dashboard_id")
                group_id_value = data.get("group_id")
                
                dashboardgroup = models.DashboardGroup.query.filter_by(
                    dashboard_id=dashboard_id_value,
                    group_id=group_id_value,
                ).first()            
                models.db.session.delete(dashboardgroup)        
                result = models.db.session.commit()                
            except Exception as ex:
                result = str(ex)                
                err_json = {
                    "dashboard_id":dashboard_id_value,
                    "group_id":group_id_value,
                    "error":result
                }
                dashboard_error.append(err_json)
        if dashboard_error:    
            result_json = {
                "code":1,
                "result":dashboard_error
            }
            return result_json
        else:
            result_json = {
                "code":0,
                "result":result
            }
            return result_json


class DashboardListResourceGroupDashboardID(BaseResource):
    pass



class DashboardResource(BaseResource):
    @require_permission("list_dashboards")
    def get(self, dashboard_id=None):
        """
        Retrieves a dashboard.

        :qparam number id: Id of dashboard to retrieve.

        .. _dashboard-response-label:

        :>json number id: Dashboard ID
        :>json string name:
        :>json string slug:
        :>json number user_id: ID of the dashboard creator
        :>json string created_at: ISO format timestamp for dashboard creation
        :>json string updated_at: ISO format timestamp for last dashboard modification
        :>json number version: Revision number of dashboard
        :>json boolean dashboard_filters_enabled: Whether filters are enabled or not
        :>json boolean is_archived: Whether this dashboard has been removed from the index or not
        :>json boolean is_draft: Whether this dashboard is a draft or not.
        :>json array layout: Array of arrays containing widget IDs, corresponding to the rows and columns the widgets are displayed in
        :>json array widgets: Array of arrays containing :ref:`widget <widget-response-label>` data

        .. _widget-response-label:

        Widget structure:

        :>json number widget.id: Widget ID
        :>json number widget.width: Widget size
        :>json object widget.options: Widget options
        :>json number widget.dashboard_id: ID of dashboard containing this widget
        :>json string widget.text: Widget contents, if this is a text-box widget
        :>json object widget.visualization: Widget contents, if this is a visualization widget
        :>json string widget.created_at: ISO format timestamp for widget creation
        :>json string widget.updated_at: ISO format timestamp for last widget modification
        """
        if request.args.get("legacy") is not None:
            fn = models.Dashboard.get_by_slug_and_org
        else:
            fn = models.Dashboard.get_by_id_and_org

        dashboard = get_object_or_404(fn, dashboard_id, self.current_org)
        response = DashboardSerializer(
            dashboard, with_widgets=True, user=self.current_user
        ).serialize()

        api_key = models.ApiKey.get_by_object(dashboard)
        if api_key:
            response["public_url"] = url_for(
                "redash.public_dashboard",
                token=api_key.api_key,
                org_slug=self.current_org.slug,
                _external=True,
            )
            response["api_key"] = api_key.api_key

        response["can_edit"] = can_modify(dashboard, self.current_user)

        self.record_event(
            {"action": "view", "object_id": dashboard.id, "object_type": "dashboard"}
        )

        return response

    @require_permission("edit_dashboard")
    def post(self, dashboard_id):
        """
        Modifies a dashboard.

        :qparam number id: Id of dashboard to retrieve.

        Responds with the updated :ref:`dashboard <dashboard-response-label>`.

        :status 200: success
        :status 409: Version conflict -- dashboard modified since last read
        """
        dashboard_properties = request.get_json(force=True)
        # TODO: either convert all requests to use slugs or ids
        dashboard = models.Dashboard.get_by_id_and_org(dashboard_id, self.current_org)

        require_object_modify_permission(dashboard, self.current_user)

        updates = project(
            dashboard_properties,
            (
                "name",
                "layout",
                "version",
                "tags",
                "is_draft",
                "is_archived",
                "dashboard_filters_enabled",
            ),
        )

        # SQLAlchemy handles the case where a concurrent transaction beats us
        # to the update. But we still have to make sure that we're not starting
        # out behind.
        if "version" in updates and updates["version"] != dashboard.version:
            abort(409)

        updates["changed_by"] = self.current_user

        self.update_model(dashboard, updates)
        models.db.session.add(dashboard)
        try:
            models.db.session.commit()
        except StaleDataError:
            abort(409)

        result = DashboardSerializer(
            dashboard, with_widgets=True, user=self.current_user
        ).serialize()

        self.record_event(
            {"action": "edit", "object_id": dashboard.id, "object_type": "dashboard"}
        )

        return result

    @require_permission("edit_dashboard")
    def delete(self, dashboard_id):
        """
        Archives a dashboard.

        :qparam number id: Id of dashboard to retrieve.

        Responds with the archived :ref:`dashboard <dashboard-response-label>`.
        """
        dashboard = models.Dashboard.get_by_id_and_org(dashboard_id, self.current_org)
        dashboard.is_archived = True
        dashboard.record_changes(changed_by=self.current_user)
        models.db.session.add(dashboard)
        d = DashboardSerializer(
            dashboard, with_widgets=True, user=self.current_user
        ).serialize()
        models.db.session.commit()

        self.record_event(
            {"action": "archive", "object_id": dashboard.id, "object_type": "dashboard"}
        )

        return d


class PublicDashboardResource(BaseResource):
    decorators = BaseResource.decorators + [csp_allows_embeding]

    def get(self, token):
        """
        Retrieve a public dashboard.

        :param token: An API key for a public dashboard.
        :>json array widgets: An array of arrays of :ref:`public widgets <public-widget-label>`, corresponding to the rows and columns the widgets are displayed in
        """
        if self.current_org.get_setting("disable_public_urls"):
            abort(400, message="Public URLs are disabled.")

        if not isinstance(self.current_user, models.ApiUser):
            api_key = get_object_or_404(models.ApiKey.get_by_api_key, token)
            dashboard = api_key.object
        else:
            dashboard = self.current_user.object

        return public_dashboard(dashboard)


class DashboardShareResource(BaseResource):
    def post(self, dashboard_id):
        """
        Allow anonymous access to a dashboard.

        :param dashboard_id: The numeric ID of the dashboard to share.
        :>json string public_url: The URL for anonymous access to the dashboard.
        :>json api_key: The API key to use when accessing it.
        """
        dashboard = models.Dashboard.get_by_id_and_org(dashboard_id, self.current_org)
        require_admin_or_owner(dashboard.user_id)
        api_key = models.ApiKey.create_for_object(dashboard, self.current_user)
        models.db.session.flush()
        models.db.session.commit()

        public_url = url_for(
            "redash.public_dashboard",
            token=api_key.api_key,
            org_slug=self.current_org.slug,
            _external=True,
        )

        self.record_event(
            {
                "action": "activate_api_key",
                "object_id": dashboard.id,
                "object_type": "dashboard",
            }
        )

        return {"public_url": public_url, "api_key": api_key.api_key}

    def delete(self, dashboard_id):
        """
        Disable anonymous access to a dashboard.

        :param dashboard_id: The numeric ID of the dashboard to unshare.
        """
        dashboard = models.Dashboard.get_by_id_and_org(dashboard_id, self.current_org)
        require_admin_or_owner(dashboard.user_id)
        api_key = models.ApiKey.get_by_object(dashboard)

        if api_key:
            api_key.active = False
            models.db.session.add(api_key)
            models.db.session.commit()

        self.record_event(
            {
                "action": "deactivate_api_key",
                "object_id": dashboard.id,
                "object_type": "dashboard",
            }
        )

# 取出所有签标，针对权限做过虑
class DashboardTagsResource(BaseResource):
    @require_permission("list_dashboards")
    def get(self):
        """
        Lists all accessible dashboards.
        """
        isadmin = is_admin(self.current_user.id)
        isowner = is_admin_or_owner(self.current_user.id)

        if isadmin:

            tags = models.Dashboard.all_tags(self.current_org, self.current_user)
            return {
                "tags": [{"name": name, "count": count} for name, count in tags],
                "uid":self.current_user.id,
                "isadmin":isadmin
            }

        elif isowner:
            # 重新实现
            sql_text = """
            select tags from public.dashboards a where exists (select 1 from public.dashboard_groups b where a.id = b.dashboard_id and b.group_id in :gid) and a.is_archived = false and a.is_draft = false and a.tags is not null        
            order by a.id desc
            """
            # 解析参数        
            group_list = tuple(self.current_user.group_ids)

            results = db.session.execute(sql_text,{"gid":group_list}).fetchall()
            # print(results)
            res_list = []
            for group in results:
                # print(group)
                for group2 in group[0]:
                    print(group2,len(group2))
                    res_list.append(group2)
            
            js = []
            x = sorted(Counter(res_list).items(),key = lambda kv:(kv[1], kv[0]),reverse=True)
            # print(x)
            for name in x:
                r = {
                "name":name[0],
                "count":name[1]
                }
                # print(r)
                js.append(r)
            new_js = {
                "tags":js,
                "uid":self.current_user.id,
                "isadmin":isadmin
            }
            return new_js
        else:
            abort(401,"你没有权限，请联系管理员申请。")       


class DashboardFavoriteListResource(BaseResource):
    def get(self):
        search_term = request.args.get("q")

        if search_term:
            base_query = models.Dashboard.search(
                self.current_org,
                self.current_user.group_ids,
                self.current_user.id,
                search_term,
            )
            favorites = models.Dashboard.favorites(
                self.current_user, base_query=base_query
            )
        else:
            favorites = models.Dashboard.favorites(self.current_user)

        favorites = filter_by_tags(favorites, models.Dashboard.tags)

        # order results according to passed order parameter,
        # special-casing search queries where the database
        # provides an order by search rank
        favorites = order_results(favorites, fallback=not bool(search_term))

        page = request.args.get("page", 1, type=int)
        page_size = request.args.get("page_size", 25, type=int)
        # TODO: we don't need to check for favorite status here
        response = paginate(favorites, page, page_size, DashboardSerializer)

        self.record_event(
            {
                "action": "load_favorites",
                "object_type": "dashboard",
                "params": {
                    "q": search_term,
                    "tags": request.args.getlist("tags"),
                    "page": page,
                },
            }
        )

        return response


# 记录用户点击报表数据
class DashboardClicks(BaseResource):
    # @require_admin_or_owner()
    def post(self):
        # 解析json参数        
        try:
            data_list = request.get_json(True)
            if not data_list:
                return {"code":1,"result":"获取点击数据异常了"}
            # print("click_jsondata:",data_list)
            user_id_value = self.current_user.id
            dashboard_id_value = data_list.get("dashboard_id")                
            click_type_value = data_list.get("click_type")
            # print("dashboard_id_value参数:",dashboard_id_value,user_id_value,click_type_value)
            
            dashboardclicks = models.DashboardClicks(
                dashboard_id=dashboard_id_value,
                user_id=user_id_value,
                click_type=click_type_value
            )            
            models.db.session.add(dashboardclicks)        
            result = models.db.session.commit()
            result_json = {
                "code":0,
                "result":result
            }
            return result_json 
        except Exception as ex:
            print(ex)
            result_json = {
                "code":1,
                "result":ex
            }
            return result_json